package com.fightingFish.user.dao.impl;

import com.fightingFish.user.bean.Administrator;
import com.fightingFish.user.bean.Gift;
import com.fightingFish.user.bean.State;
import com.fightingFish.user.bean.User;
import com.fightingFish.user.dao.AdministratorDao;
import com.fightingFish.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdministratorDaoImpl implements AdministratorDao {

    /**
     * 通过用户名查询对应的对象 并返回该Administrator对象
     *
     * @param account
     * @return Administrator对象
     */
    @Override
    public Administrator queryAdministrator(String account) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Administrator administrator = null;
        //定义sql语句
        String sql = "select anminId,account,pwd from administrator where account = ?";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setString(1, account);
            //执行sql语句
            rs = ps.executeQuery();
            if (rs.next()) {
                //创建administrator对象
                administrator = new Administrator();
                administrator.setAnminId(rs.getInt("anminId"));
                administrator.setAccount(rs.getString("account"));
                administrator.setPwd(rs.getString("pwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return administrator;
    }

    /**
     * 根据用户ID管理用户状态
     *
     * @param userId
     * @param state
     * @return
     */
    @Override
    public boolean userManage(int userId, int state) {
        Connection con = null;
        PreparedStatement ps = null;
        int row = 0;
        //定义sql语句
        String sql = "UPDATE user SET state = ? WHERE userId = ?";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, state);
            ps.setInt(2, userId);
            //执行sql语句
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return row > 0;
    }

    /**
     * 查询所有礼物
     *
     * @return 礼物集合
     */
    @Override
    public List<Gift> queryGiftAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Gift gift = null;
        ArrayList<Gift> list = new ArrayList<>();
        //定义sql语句
        String sql = "select * from gift ";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建gift对象
                gift = new Gift();
                gift.setGiftId(rs.getInt("giftId"));
                gift.setGiftName(rs.getString("giftName"));
                gift.setGiftImgUrl(rs.getString("giftImgUrl"));
                gift.setGiftPrice(rs.getDouble("giftPrice"));
                list.add(gift);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return list;
    }

    /**
     * 添加礼物
     *
     * @param giftName
     * @param giftPrice
     * @param giftImgUrl
     * @return
     */
    @Override
    public boolean addGift( String giftName, double giftPrice, String giftImgUrl) {
        Connection con = null;
        PreparedStatement ps = null;
        int row;
        //定义sql语句
        String sql = "INSERT INTO `gift`(giftName,giftPrice,giftImgUrl) VALUES (?,?,?);";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //给占位符赋值

            ps.setString(1, giftName);
            ps.setDouble(2, giftPrice);
            ps.setString(3, giftImgUrl);
            //执行sql语句
            row = ps.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return false;
    }

    /**
     * 移除礼物
     *
     * @param giftId
     * @return
     */
    @Override
    public boolean removeGift(int giftId) {
        Connection con = null;
        PreparedStatement ps = null;
        int row;
        //定义sql语句
        String sql = "DELETE  FROM `gift` WHERE giftId=?;";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, giftId);
            //执行sql语句
            row = ps.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return false;
    }

    /**
     * 修改礼物信息
     *
     * @param giftId
     * @param giftName
     * @param giftPrice
     * @param giftImgUrl
     * @return 需前端判断输入的值不能为空
     */
    @Override
    public boolean updateGift(int giftId, String giftName, double giftPrice, String giftImgUrl) {
        Connection con = null;
        PreparedStatement ps = null;
        int row;
        //定义sql语句
        String sql = "UPDATE gift SET giftName=?,giftPrice=?,giftImgUrl=? WHERE giftId = ?";
        try {
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setString(1, giftName);
            ps.setDouble(2, giftPrice);
            ps.setString(3, giftImgUrl);
            ps.setInt(4, giftId);
            //执行sql语句
            row = ps.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return false;
    }

    /**
     * 方法概述： 获取所有普通用户信息
     * @return
     */
    @Override
    public List<User> getUserAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        ArrayList<User> list = new ArrayList<>();
        //定义sql语句
        try {
            String sql = "select * from user u join state s where u.state=s.stateId and u.anchorOrUser is null ";
            //创建连接
            con = JdbcUtil.getConnection();
            //获取预编译对象
            ps = con.prepareStatement(sql);
            //执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建User对象
                user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setNickName(rs.getString("nickName"));
                user.setPhotoImgUrl(rs.getString("photoImgUrl"));
                user.setAnchorOrUser(rs.getInt("anchorOrUser"));
                State state = new State(rs.getString("stateName"));
                user.setState(state);
                user.setCreateTime((rs.getTime("createTime")));
                user.setBalance(rs.getDouble("balance"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));

                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return list;
    }


    /**
     * 获取全部礼物信息
     * @return
     */
    @Override
    public List<Gift> getGift() {
        Connection con=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Gift> list =  new ArrayList<>();
        Gift gift = null;
        try{
            String sql= "select * from gift ";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                gift = new Gift();
                gift.setGiftId(rs.getInt("giftId"));//礼物id
                gift.setGiftName(rs.getString("giftName"));//名字
                gift.setGiftPrice(rs.getDouble("giftPrice"));//价格
                gift.setGiftImgUrl(rs.getString("giftImgUrl"));//图片地址
                list.add(gift);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con,ps,rs);
        }
        return list;
    }

    /**
     * 方法概述： 获取消费清单的总金额
     * @return
     */
    @Override
    public List<Double> getPriceAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Double> list=new ArrayList<>();
        try {
            String sql = "SELECT r.rocNums,g.giftPrice from recordsofconsumption r JOIN gift g where r.rocGift= g.giftId";
            con= JdbcUtil.getConnection();
            ps=con.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                double count = rs.getDouble("rocNums");
                double price = rs.getDouble("giftPrice");
                price = count *price;
                list.add(price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(con,ps,rs);
        }
        return list;
    }

    @Override
    public List<Double> rechargeAmount() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Double> list=new ArrayList<>();
        try {
            String sql = "SELECT balance from user";
            con= JdbcUtil.getConnection();
            ps=con.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                double balance = rs.getDouble("balance");
                System.out.println(balance);
                list.add(balance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(con,ps,rs);
        }
        return list;
    }

}
